import urllib2, os
from BeautifulSoup import *
from urlparse import urljoin
from pysqlite2 import dbapi2 as sqlite
import pickle
from stemmer import PorterStemmer

stemmerObj = PorterStemmer()

# creating list of stopwords

f = open('stopwords','r')
stopwords = pickle.load(f)
f.close()

# creating database

dbname = 'database_test_11'

def create_db(name):
	db = sqlite.connect(name)
	db.execute('create table urllist(url,docid, num_outlinks integer)')
	db.execute('create table link(fromid integer, toid integer, flag integer)')
	db.execute('create table wordlist(word)')
	db.execute('create table linkword(wordid integer, urlid integer, freq integer)')
	db.execute('create index urlx on urllist(url)')
#	db.execute('create index docidx on urllist(docid)')
	db.execute('create index urltoidx on link(toid)')
	db.execute('create index urlfromidx on link(fromid)')
	db.execute('create index wordx on wordlist(word)')
	db.execute('create index wordidx on linkword(wordid)')
	db.execute('create index urlidx on linkword(urlid)')
	db.commit()

# removes port from the given URL
def remPort(url) :
	l = url.split(':')
	if len(l) > 2 :
		t = l[2].find('/')
		url = l[0] + ':' + l[1] + l[2][t:]
	return url

# checks if the url is added in the table urllist or not, state = 0 if not, 
# state = 1 if added (as a doc, i.e. docid and outlinks are added as well), 
# state = 2 if docurl is added but other details docid and outlinks is not
def isIndexed(url) :
	query = "select docid from urllist where url=\"" + url + "\""
	temp = db.execute(query).fetchone()
	if temp != None:
		state = 1
		if temp[0] == 'None':
			state = 2
	else :
		state = 0
	return state
def separatewords(text,worddict,wordstring,All):
	splitter = re.compile('\\W*')
	for s in splitter.split(text):
		if (s!='' and s not in stopwords):
			s=stemmerObj.stem(s.lower(),0,len(s)-1);
			if(worddict.has_key(s)):
				worddict[s]+=1;
			else:
				worddict[s]=1;
				wordstring= wordstring +",'"+str(s)+"'";
				All.append(str(s));
#create_db(dbname)
db = sqlite.connect(dbname)

path = './../trec_data_set'
trec_list = os.listdir(path)

dir_count = 0
for i in trec_list:
	dir_count = dir_count + 1
	path_trec = path + '/' + i
#	print "#####################################################################\t" + str(dir_count)
#	print "#####################################################################"
#	print 'Opening Directory----->' + path_trec
	B_list = os.listdir(path_trec)

	file_count = 0
	for j in B_list:
		file_count = file_count + 1
		path_B = path_trec + '/' + j
		print "#####################################################################\t" + str(dir_count) + '\t' + str(file_count)
		print 'Opening file------->' + path_B

		temp_count = 0
		fp = open(path_B,"r")
		data = fp.read()
		docs=data.lower().split("</doc>");
		for l in docs:
#			print "Rishab";
			try:
				soup=BeautifulSoup(l+"</doc>");
			except:
				temp_count += 1
#				print "Number of unread files:" + str(temp_count)
				continue;
			list_links = soup('a')
			docno = str(soup.docno)
			dochdr = str(soup.dochdr)

			t = (dochdr[8:]).split(' ')
			docurl = t[0]
			docid = docno[7:-8].strip()
			
			docurl = docurl.replace('"','')
			docurl = docurl.replace("'",'')
			docurl = remPort(docurl).strip()
			print docurl + '\t' + docno

			out = 0
			url_list = []
			
			for link in list_links:
				if ('href' in dict(link.attrs)):
					url = urljoin(docurl,link['href'])
#					if url.find("'") != -1: 
#						continue
					url = url.split('#')[0]
					url = url.replace('"','')
					url = url.replace("'","")
					if url[0:4] == 'http':
						try :
							url = str(url)
						except :
		#				 	url = repr(url)
		#				 	tempUrl = url.split('>')
		#				 	url = tempUrl[0].replace("u'",'')
							continue
						url = remPort(url)
						url_list.append(url)
						out = out + 1
#					print "Indexing link %s" % url
#						query1="insert into link (fromid,toid) values(\""+str(docurl)+"\",\""+str(url)+"\")"
#						db.execute(query1)
#						out = out + 1

			state = isIndexed(docurl)
#			print state
#			print "Rishab State";
			if state == 0 :
				query = "insert into urllist (url,docid,num_outlinks) values(\""+str(docurl)+"\",\""+str(docid)+"\","+str(out)+")"
				db.execute(query)
#				db.commit()
			elif state == 2 :
				query = "update urllist set docid='" + str(docid) + "',num_outlinks=" + str(out) + " where url='" + str(docurl) + "'"
				db.execute(query)
#				db.commit()
			
			docid = 'None'
			out = 0
			
			query = "select rowid from urllist where url='" + str(docurl) + "'"
			res = db.execute(query).fetchone()
			fromid = res[0]
			for url in url_list :
				state = isIndexed(url)
				if state == 0 :
					query = "insert into urllist (url,docid,num_outlinks) values(\""+str(url)+"\",\""+str(docid)+"\","+str(out)+")"
					db.execute(query)
#					db.commit()
#				print "Indexing link " + str(url) + "\t\t" + str(state)
				
				query = "select rowid from urllist where url='" + str(url) + "'"
#				db.execute(query)
				res = db.execute(query).fetchone()
				toid = res[0]
				
				query = "select rowid from link where fromid=" + str(fromid) + " and toid=" + str(toid)
				temp = db.execute(query).fetchone()
				if temp == None :
					query = "insert into link (fromid,toid,flag) values(" + str(fromid) + "," + str(toid) + ",0)"
					db.execute(query)
#					db.commit()
			
#			print "Rishab 2"
			doc_text = soup.findAll(text=True)
#			print doc_text;
			worddict = {};
			wordstring = "";
			All = [];
			for text in doc_text :
				separatewords(text,worddict,wordstring,All)
			
#			res=db.execute("select word from wordlist where word in ("+wordstring[1:]+") ").fetchall();
#			existing = [];
#			for i in res:
#				existing.append(i[0]);
#			nonexisting = set(All).difference(set(existing));
#			nonexistingstring = ""
#			for i in nonexisting:
#				nonexistingstring = nonexistingstring + ",('"+i+"')";
#			print nonexistingstring
#			query = "insert into wordlist (word) values "+nonexistingstring[1:];
#			print query;
#			db.execute(query);
#			db.commit();
#
#			res=db.execute("select word,rowid from wordlist where word in ("+wordstring[1:]+") ").fetchall();


#frequencystring = "";
#			for i in res:
#				frequencystring=frequency + ",('"+i[1]+"','"+str(fromid)+"','"+str(worddict[i[0]])+"') ";
#			query = "insert into linkword (wordid,urlid,freq) values "+frequencystring[1:];
#			db.execute(query)
#			db.commit()
#





#for i in worddict.keys():

			
				

			for i in worddict.keys():
			
			       query = "select rowid from wordlist where word='" + str(i) + "'"
			       temp = db.execute(query).fetchone()
			       if temp == None :
				       query = "insert into wordlist (word) values('" + str(i) + "')"
				       db.execute(query)
#				       db.commit()
				       query = "select rowid from wordlist where word='" + str(i) + "'"
				       res = db.execute(query).fetchone()
				       wordid = res[0]
			       else :
				       wordid = temp[0]
    			       query = "insert into linkword (wordid,urlid,freq) values(" + str(wordid) + "," + str(fromid) + ","+str(worddict[i])+")"
			       db.execute(query)
#			       db.commit()
			
		

#				print "length of words" + str(len(words));
#			print "Rishab 2.5"
#			count10=0;
#			for word in words :
#				count10+=1;
#				if word not in stopwords :
#					lp = 0
#					word = word.lower()
#					word = stemmerObj.stem(word,0,len(word)-1)
#						print word
#					query = "select rowid from wordlist where word='" + str(word) + "'"
#					temp = db.execute(query).fetchone()
#					if temp == None :
#						query = "insert into wordlist (word) values('" + str(word) + "')"
#						db.execute(query)
#						db.commit()
#						query = "select rowid from wordlist where word='" + str(word) + "'"
#						res = db.execute(query).fetchone()
#						wordid = res[0]
#					else :
#						wordid = temp[0]
#					query = "select freq from linkword where wordid=" + str(wordid) + " and urlid=" + str(fromid)
#					temp = db.execute(query).fetchone()
#					if temp == None :
#						query = "insert into linkword (wordid,urlid,freq) values(" + str(wordid) + "," + str(fromid) + ",1)"
#						db.execute(query)
#						db.commit()
#					else :
#						lp = temp[0] + 1
#						db.execute('update linkword set freq=%d where wordid=%d and urlid=%d' % (lp,wordid,fromid))
#						db.commit()
#				print count10;
#			print "Rishab 3"
#			print '\n'
#		fp.close()
db.commit()
print "Its over"
